﻿Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient

<System.ComponentModel.DataObjectAttribute(True)> _
Public Class BLL_ManageWorkRecord_SearchVolunteerControl_AddDirtyHeader

    Public Sub New()

    End Sub

    'SELECT 
    <System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.[Select])> _
    Public Function BLL_Select(ByVal WorkHeaderID As Integer, ByVal UOrganizationID As String, ByVal VOrganizationID As Integer, ByVal VolunteerId As String, ByVal VolunteerName As String, ByVal TEL As String) As DataTable

        Dim dat As New clsData

        Dim sql As New MyStringBuilder

        sql.AppendFormat("SELECT A.VolunteerID,A.VolunteerName,CASE WHEN B.VolunteerID is null THEN '1' ELSE '0' END as IsEnable ")
        sql.AppendFormat("from VolunteerDistinctView A ")
        sql.AppendFormat("LEFT JOIN (SELECT C.* FROM WorkHourRecord C INNER join WorkRecord D on C.RecordID=D.RecordID and C.WorkType='FindDirtyHeader' and D.WorkHeaderID='{0}') B on A.VolunteerID = B.VolunteerID ", WorkHeaderID)

        sql.AppendFormat("  left JOIN (select vm.VOrganizationID,vo.VOrganizationName,vm.VolunteerID,vo.DirectUOrganizationID from VOrganizationMapping vm ")
        sql.AppendFormat("inner join VOrganizationViewV2 vo on vm.VOrganizationID=vo.VOrganizationID ) oo on A.VolunteerID=oo.VolunteerID  ")
        sql.AppendFormat("WHERE A.Active ='Y' ")
        If VOrganizationID <> "0" Then
            sql.AppendFormat("AND (oo.VOrganizationID = {0} ) ", VOrganizationID)
        End If

        If UOrganizationID <> "0" Then
            sql.AppendFormat("AND (oo.DirectUOrganizationID = '{0}' ) ", UOrganizationID)
        End If
        'sql.AppendFormat("AND (A.VolunteerID IN (SELECT distinct VolunteerID FROM VOrganizationMapping WHERE VOrganizationID={0}  ) or '{0}' =0) ", VOrganizationID)
        'sql.AppendFormat("AND (A.VillageID IN (SELECT RegionID FROM Region WHERE Parent='{0}' or '{0}' ='' )) ", Region)
        'sql.AppendFormat("AND (A.VillageID ='{0}' or '{0}' ='' ) ", Region2)
        If VolunteerId <> "" Then
            sql.AppendFormat("AND (A.VolunteerId = '{0}' ) ", VolunteerId)
        End If

        If VolunteerName <> "" Then
            sql.AppendFormat("AND (A.VolunteerName like '%{0}%' ) ", VolunteerName)

        End If

        If TEL <> "" Then
            sql.AppendFormat("AND (((A.MobilePhone like '%{0}%' ) or (A.OfficePhone like '%{0}%' ))) ", TEL)
        End If

        sql.AppendFormat("ORDER BY IsEnable desc,A.VolunteerID asc")

        Return dat.GetData(sql)

    End Function

End Class
